package ua.kiev.tosik.price.database.connectors;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.time.DurationFormatUtils;
import ua.kiev.tosik.price.conf.AppConfig;
import ua.kiev.tosik.price.database.tools.ScriptRunner;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class ConnectorDerby extends ConnectorFactory {

	private static ConnectorDerby ourInstance;
	private Properties dbProperties;
	private AppConfig config;
	private final String databaseSQL = "conf/sql/Derby.sql";

	private String derbySystemHome, dbName, dbUrl;

	public static ConnectorDerby getInstance() {
		if (ourInstance == null) {
			ourInstance = new ConnectorDerby();
		}
		return ourInstance;
	}

	private ConnectorDerby() {
		long startTime = System.currentTimeMillis();
		logger.info("Initialize connector for Derby database");
		config = AppConfig.getInstance();
		dbProperties = new Properties();

		derbySystemHome = config.get("derby.system.home");
		dbName = config.get("derby.dbname");
		dbUrl = "jdbc:derby:" + dbName;

		dbProperties.put("derby.storage.pageCacheSize", "8192");
		dbProperties.put("derby.language.logQueryPlan", "true");
		dbProperties.put("derby.language.logStatementText", "true");
		dbProperties.put("user", AppConfig.getInstance().get("derby.user"));
		dbProperties.put("password", AppConfig.getInstance().get("derby.password"));

		try {
			openConnection();
		} catch (Exception e) {
			setErrorMessage(e.getMessage());
			logger.error(e);
		}
		logger.debug("Connector initialization duration: " + DurationFormatUtils.formatDuration((System.currentTimeMillis()-startTime), "s.S") + " sec");
		logger.debug("Database size: " + numberFormat.format(FileUtils.sizeOfDirectory(new File(derbySystemHome + "/" + dbName))/1024) + " Kb" );
	}

	private void openConnection() throws Exception {
		boolean createTables = false;
		if ( !dbExists() ) {
			dbProperties.put("create", "true");
			createTables = true;
		}
		logger.debug("Try to open database");
		connection = DriverManager.getConnection(dbUrl, dbProperties);
		// connection.setAutoCommit(false);
		dbProperties.remove("create");
		logger.debug("Derby database connection opened!");

		if ( createTables ) {
			createTables();
		}

	}

	@Override
	public void compressTables(String tableName) throws SQLException {
		long startTime = System.currentTimeMillis();
		connection.commit();
		// logger.debug("Compressing table [" + tableName + "]");
		try {
			CallableStatement cs = connection.prepareCall("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(CURRENT_USER, ?, ?)");
			cs.setString(1, tableName);
			cs.setShort(2, (short)1);
			cs.execute();
			connection.commit();
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
		}
		logger.debug("Compressing table [" + tableName + "]: " + DurationFormatUtils.formatDuration((System.currentTimeMillis()-startTime), "s.S") + " sec");
	}

	@Override
	public void close() throws SQLException {
		dbProperties.remove("create");
		logger.debug("Shutdown Derby database");
		connection.close();
		dbProperties.put("shutdown", "true");
		connection = DriverManager.getConnection(dbUrl, dbProperties);
		connection = null;
		logger.debug("Derby database connection closed!");
	}

	@Override
	public long getDatabaseSize() {
		return (FileUtils.sizeOfDirectory(new File(derbySystemHome + "/" + dbName))/1024);
	}

	private boolean dbExists() {
		boolean dbExists = false;
		File db = new File(derbySystemHome + "/" + dbName);
		if ( db.exists() ) {
			logger.debug("DerbyDb catalog found");
			dbExists = true;
		} else {
			logger.warn("DerbyDb catalog not found [" + db.getAbsolutePath() + "]. Trying to create it");
		}
		return dbExists;
	}

	private boolean createTables() {
		logger.info("Creating database tables...");
		boolean bCreatedTables = false;
		ScriptRunner scriptRunner = new ScriptRunner(connection, true, true);

		try {
			scriptRunner.runScript(new BufferedReader(new FileReader(databaseSQL)));
			connection.commit();
			logger.info("Database schema created!");
			bCreatedTables = true;
		} catch (Exception e) {
			logger.fatal("Can't create database schema! " + e.getLocalizedMessage());
			logger.error(e);
		}
		/*
		try {
			PreparedStatement ps = connection.prepareStatement(getStructure(databaseSQL));
			ps.executeUpdate();
			// Statement statement = connection.createStatement();
			// statement.execute(getStructure(databaseSQL));
			ps.close();
			connection.commit();
			bCreatedTables = true;
			logger.info("Database schema created!");
		} catch (SQLException ex) {
			logger.fatal("Can't create database schema! " + ex.getLocalizedMessage());
			ex.printStackTrace();
		} catch (Exception e) {
			logger.fatal("Can't create database schema! " + e.getLocalizedMessage());
			e.printStackTrace();
		} */
		return bCreatedTables;
	}

	/*
	* For show tables in current schema (for Derby)
	* SELECT schemaname, tablename, SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename) FROM sys.sysschemas s, sys.systables t
	* WHERE s.schemaid = t.schemaid AND schemaname = CURRENT SCHEMA;
	*
	* To compress table:
	* conn.setAutoCommit(true); // !!
	* CallableStatement cs = conn.prepareCall("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");
		cs.setString(1, "US");
		cs.setString(2, "CUSTOMER");
		cs.setShort(3, (short) 1);
		cs.execute();
	* */

}
